from math import ceil

from sqlalchemy import create_engine
import pymysql
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker

pymysql.install_as_MySQLdb()  # MySQL驱动
# 数据库连接URL格式: 数据库类型://用户名:密码@ip:端口/数据库名称
engine = create_engine('mysql://root:root@127.0.0.1:3306/ssq', pool_size=100,
                       max_overflow=100,
                       # pool_recycle=7200,
                       pool_recycle=1,
                       pool_timeout=7200,
                       echo=False)  # 数据库连接对象
Session = sessionmaker(bind=engine)  # 会话
session = Session()  # 会话对象
Base = declarative_base()  # 模型基类


class Ssq(Base):
    """
    双色球模型类
    """
    __tablename__ = 'ssq'
    id = Column(Integer, primary_key=True)
    public_date = Column(String)  # 开奖日期
    public_num = Column(Integer)  # 开奖期号
    red_ball = Column(String)  # 红球号码
    blue_ball = Column(String)  # 篮球号码

    def __repr__(self):
        return "Ssq(public_date='%s', public_num='%s', red_ball='%s, blue_ball='%s')" % (
            self.public_date, self.public_num, self.red_ball, self.blue_ball)


class SsqDb:
    def all(self):
        """
        返回数据库中所有的双色球数据
        """
        return session.query(Ssq).all()

    def is_exists(self, public_date):
        """
        更加开奖日期判断数据是否存在于数据库
        :param public_date: 开奖日期
        """
        result = session.query(Ssq).filter_by(public_date=public_date).first()
        return result != None

    def page(self, page, size=20):
        """
        分页查询
        :param page:第几页
        :param size: 每页数量
        :return: 分页数据
        """
        page_data = session.query(Ssq).order_by(Ssq.public_date.desc()).limit(size).offset(page * size)
        page_data = [i for i in page_data]
        return {
            'code': 0,
            'msg': 'ok',
            'count': self.count(),
            'data': page_data
        }

    def count(self):
        """
        查询数据库中的记录总数
        """
        count = session.query(Ssq).count()
        return count

    def add_by_tuple(self, ssq_tuple):
        ssq = Ssq()
        ssq.public_date = ssq_tuple[0]
        ssq.public_num = ssq_tuple[1]
        ssq.red_ball = ssq_tuple[2]
        ssq.blue_ball = ssq_tuple[3]
        if not self.is_exists(ssq.public_date):
            session.add(ssq)
            session.commit()
            print("添加成功")
        else:
            print("数据已存在")

    def get_latest_red_tuple(self):
        """
        获取最新的红球数据,并转换为元组
        """
        red_ball = session.query(Ssq.red_ball).order_by(Ssq.public_date.desc()).first()
        red_ball_list = red_ball[0].split(',')
        red_ball_tuple = map(lambda x: int(x), red_ball_list)
        red_ball_tuple = tuple(red_ball_tuple)
        return red_ball_tuple

    def get_latest_5_blue_tuple(self):
        """
        获取最新5期的篮球数据
        """
        blue_ball = session.query(Ssq.blue_ball).order_by(Ssq.public_date.desc())[:5]
        blue_ball_tuple = tuple(map(lambda x: int(x[0]), blue_ball))
        return blue_ball_tuple

    def get_red_tuple_by_public_num_tuple(self, public_num):
        """
        根据双色球期号获取红球数据
        :param public_num: 双色球期号
        """
        result = session.query(Ssq.red_ball).filter_by(public_num=public_num).first()
        red_ball_tuple = tuple(result[0].split(','))
        red_ball_tuple = tuple(map(lambda x: int(x), red_ball_tuple))
        return red_ball_tuple

    def get_before_5_blue_tuple_by_public_num(self, public_num):
        """
        根据指定的期号获取篮球数据
        :param public_num: 指定期号
        """
        blue_ball = session.query(Ssq.blue_ball).filter(Ssq.public_num < public_num).order_by(
            Ssq.public_date.desc())[:5]
        blue_ball_tuple = tuple(map(lambda x: int(int(x[0])), blue_ball))
        return blue_ball_tuple

    def get_public_num_all(self):
        """
        获取所有的双色球开奖期号
        """
        public_num_list = session.query(Ssq.id, Ssq.public_num).order_by(Ssq.public_num.desc()).all()
        return public_num_list

    def get_ball_data_by_id(self, id):
        """
        根据ID获取篮球和红球数据
        :param id: ID
        """
        ball = session.query(Ssq.public_num, Ssq.red_ball, Ssq.blue_ball).filter_by(id=id).first()
        return ball


# Base.metadata.create_all(engine)
if __name__ == '__main__':
    ssqDb = SsqDb()
    # 查询最新的红球
    print(ssqDb.get_ball_data_by_id(2621))
